﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using OgilvyOne.Borad.IDAL;

namespace OgilvyOne.Borad.DAL
{
    public class PreOrderDAL : IDALPreOrder
    {
        private const string ADD_SQL = "INSERT INTO preorder(name,sex,mobile,email,province,city,dealer,budget,mention,capcity,carModel,carColor,carSeat,creatTime,source,channel) VALUES(@name,@sex,@mobile,@email,@province,@city,@dealer,@budget,@mention,@capcity,@carModel,@carColor,@carSeat,getDate(),@source,@channel);select @@IDENTITY";
        public int Add(string name,string sex, string mobile, string email, string province, string city, string dealer, string budget, string mention, string capcity, string carModel, string carColor, string carSeat, string source,string channel)
        {
            int id = 0;
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@name",SqlDbType.NVarChar),
                new SqlParameter("@sex",SqlDbType.NVarChar),
                new SqlParameter("@mobile",SqlDbType.NVarChar),
                new SqlParameter("@email",SqlDbType.NVarChar),
                new SqlParameter("@province",SqlDbType.NVarChar),
                new SqlParameter("@city",SqlDbType.NVarChar),
                new SqlParameter("@dealer",SqlDbType.NVarChar),
                new SqlParameter("@budget",SqlDbType.NVarChar),
                new SqlParameter("@mention",SqlDbType.NVarChar),
                new SqlParameter("@capcity",SqlDbType.NVarChar),
                new SqlParameter("@carModel",SqlDbType.NVarChar),
                new SqlParameter("@carColor",SqlDbType.NVarChar),
                new SqlParameter("@carSeat",SqlDbType.NVarChar),
                new SqlParameter("@source",SqlDbType.NVarChar),
                new SqlParameter("@channel",SqlDbType.NVarChar)
            };
            parms[0].Value = name;
            parms[1].Value = sex;
            parms[2].Value = mobile;
            parms[3].Value = email;
            parms[4].Value = province;
            parms[5].Value = city;
            parms[6].Value = dealer;
            parms[7].Value = budget;
            parms[8].Value = mention;
            parms[9].Value = capcity;
            parms[10].Value = carModel;
            parms[11].Value = carColor;
            parms[12].Value = carSeat;
            parms[13].Value = source;
            parms[14].Value = channel;
            string s = SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC,CommandType.Text,ADD_SQL,parms).ToString();
            id = int.Parse(s);
            return id;
        }

        public int IsExist(string email)
        {
            string sql = "select count(email) from preorder where email=@email";

            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@email",SqlDbType.NVarChar)
            };
            parms[0].Value = email;
            object obj = SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms);
            return Convert.ToInt32(obj.ToString());
        }


        public DataSet Query()
        {
            string sql = "SELECT d.ID,d.DEALER_NAME,d.DEALER_CODE,d.PROVINCE,d.CITY,d.TEL,d.MORE_TEL ,d.ADDRESS ,d.POSTCODE ,d.CACTCONT_PRO ,d.DEALER_DESC ,d.ORDER_NUM ,d.ADDTIME,d.ADDUSER,pc.cnname as pcname,cc.cnname as ccname FROM VICO_DEALER as d left join CMS_DICTIONARY as pc on d.PROVINCE=pc.CID left join CMS_DICTIONARY as cc on d.CITY=cc.CID ";
            //sql += " where 1=1";
            //if (q != null)
            //{
            //    if (!string.IsNullOrEmpty(q.Dealernam))
            //    {
            //        sql += " and d.DEALER_NAME like '%" + StringUtil.ReplaceSecStr(q.Dealernam) + "%'";
            //    }
            //    if (q.City > 0)
            //    {
            //        sql += " and d.CITY = " + q.City;
            //    }
            //    if (q.Province > 0)
            //    {
            //        sql += " and d.PROVINCE = " + q.Province;
            //    }
           // }
            sql += " order by pcname,ccname,ORDER_NUM,DEALER_NAME";
            return SQLHelper.Query(sql);
        }


        public DataTable GetProvince() 
        {
            string strSql = "SELECT distinct d.PROVINCE,pc.cnname as pcname FROM VICO_DEALER as d left join CMS_DICTIONARY as pc on d.PROVINCE=pc.CID left join CMS_DICTIONARY as cc on d.CITY=cc.CID  order by pcname";
            return SQLHelper.Query(strSql).Tables[0];
        }
    }
}
